package com.crm.dao.impl;

import com.crm.dao.DepartmentDao;
import com.crm.model.Department;
import com.crm.model.PageBean;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


@Repository("departmentDao")
public class DepartmentDaoImpl implements DepartmentDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Department> list(PageBean pageBean, Department s_department) {
        StringBuilder sb = new StringBuilder("select * from t_department");
        if (s_department != null) {
            if (StringUtils.isNotEmpty(s_department.getDeptName())) {
                sb.append(" and deptName like '%").append(s_department.getDeptName()).append("%'");
            }
        }
        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getPageSize());
        }
        final List<Department> departmentList = new ArrayList<Department>();
        jdbcTemplate.query(sb.toString().replaceFirst("and", "where"), new Object[]{}, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Department department = new Department();
                department.setId(rs.getInt("id"));
                department.setDeptName(rs.getString("deptName"));
                department.setRemark(rs.getString("remark"));
                departmentList.add(department);
            }
        });
        return departmentList;
    }

    @Override
    public int count(Department s_department) {
        StringBuilder sb = new StringBuilder("select count(*) from t_department");
        if (s_department != null) {
            if (StringUtils.isNotEmpty(s_department.getDeptName())) {
                sb.append(" and deptName like '%").append(s_department.getDeptName()).append("%'");
            }
        }
        return jdbcTemplate.queryForObject(sb.toString().replaceFirst("and", "where"), Integer.class);
    }

    @Override
    public void add(Department department) {
        String sql = "insert into t_department values(null,?,?)";
        jdbcTemplate.update(sql, new Object[]{department.getDeptName(), department.getRemark()});
    }

    @Override
    public void update(Department department) {
        String sql = "update t_department set deptName=?,remark=? where id=?";
        jdbcTemplate.update(sql, new Object[]{department.getDeptName(), department.getRemark(), department.getId()});
    }

    @Override
    public void delete(int id) {
        String sql = "delete from t_department where id=?";
        jdbcTemplate.update(sql, new Object[]{id});
    }

    @Override
    public Department loadById(int id) {
        String sql = "select * from t_department where id=?";
        final Department department = new Department();
        jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                department.setId(rs.getInt("id"));
                department.setDeptName(rs.getString("deptName"));
                department.setRemark(rs.getString("remark"));
            }
        });
        return department;
    }
}
